/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package dao;

import domain.Mobileconsumption;
import domain.Mobilecontract;
import java.sql.Date;
import java.util.List;
import javax.persistence.EntityManager;
import javax.persistence.EntityTransaction;

/**
 *
 * @author Alberto
 */
public class MobileConsumptionDao {

    EntityManager em;
    EntityTransaction tx;

    public MobileConsumptionDao() {
        em = SingletonDao.getInstance().getEntityManager();
        tx = SingletonDao.getTx();
    }

    public List<Mobileconsumption> getListMobileconsumptionDate6(Mobilecontract m) {
        Date date = new Date(new java.util.Date().getTime());
        int month = date.getMonth();
        month = month -7;
        List list = em.createNativeQuery("select * from mobileconsumption as m, contract as cont, consumption as cons where cont.idContract='"+m.getContract().getIdContract()+"' and  m.consFK=cons.idCons and cons.consFk=cont.idContract and m.dateTo>"+date.getDate()+"-"+month+"-"+date.getYear(), Mobileconsumption.class).getResultList();
        return list;
    }

    public Mobileconsumption getMobileconsumption(Integer idMobileconsumption){
        Mobileconsumption m = (Mobileconsumption) em.createNativeQuery("select * from mobileconsumption as m where m.idMobileCons='" + idMobileconsumption + "'", Mobileconsumption.class).getSingleResult();
        return m;
    }

    public void saveMobileconsumption(Mobileconsumption m) {
        tx.begin();
        em.persist(m);
        tx.commit();
    }

    public void removeMobileconsumption(Mobileconsumption m) {
        tx.begin();
        em.remove(m);
        tx.commit();
    }

    public List<Mobileconsumption> getListMobileconsumption(Mobilecontract m){
        List<Mobileconsumption> list = em.createNativeQuery("select * from mobileconsumption as m, contract as cont, consumption as cons where cont.idContract='"+m.getContract().getIdContract()+"' and  m.consFK=cons.idCons and cons.consFk=cont.idContract", Mobileconsumption.class).getResultList();
        return list;
    }

    public Mobileconsumption getMobileconsumptionDate(Mobilecontract mc){
        java.util.Date date = new java.util.Date();
        int year = date.getYear()+1900;
        int month = date.getMonth()+1;
        int day = date.getDate();
        Mobileconsumption m = null;
        List list = em.createNativeQuery("select * from mobileconsumption as mcons, consumption as cons, mobilecontract as mc, contract as c where mcons.consFK = cons.idCons and cons.consFK = c.idContract and c.idContract = "+mc.getContract().getIdContract()+" and mc.idMobileContract = "+mc.getIdMobileContract()+" and mc.contractFK = c.idContract and mcons.dateTo > '"+year+"-"+month+"-"+day+"'", Mobileconsumption.class).getResultList();
        if(!list.isEmpty()){
            m = (Mobileconsumption) list.get(0);
        }
        return m;
    }

    public List<Mobileconsumption> getListMobileconsumptionDate3(Mobilecontract m){
        Date date = new Date(new java.util.Date().getTime());
        int month = date.getMonth();
        month = month -4;
        List list = em.createNativeQuery("select * from mobileconsumption as m, contract as cont, consumption as cons where cont.idContract='"+m.getContract().getIdContract()+"' and  m.consFK=cons.idCons and cons.consFk=cont.idContract and m.dateTo>"+date.getDate()+"-"+month+"-"+date.getYear(), Mobileconsumption.class).getResultList();
        return list;
    }

    public Mobileconsumption getLastMobileConsumption(Mobilecontract mc){
        java.util.Date date = new java.util.Date();
        int year = date.getYear()+1900;
        int month = date.getMonth()+1;
        int day = date.getDate();
        Mobileconsumption inv = (Mobileconsumption) em.createNativeQuery("select * from mobileconsumption as mcons, consumption as cons, mobilecontract as mc, contract as c where mcons.consFK = cons.idCons and cons.consFK = c.idContract and c.idContract = "+mc.getContract().getIdContract()+" and mc.idMobileContract = "+mc.getIdMobileContract()+" and mc.contractFK = c.idContract and mcons.dateTo > '"+year+"-"+month+"-"+day+"'", Mobileconsumption.class).getSingleResult();
        return inv;
    }

}
